<?php

namespace app\admin\controller\scanwork;

use app\common\controller\Backend;
use think\Db;

/**
 * 成本核算
 * @icon fa fa-jpy
 */
class Cost extends Backend
{
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\scanwork\OrderCost;
    }

    /**
     * 订单成本
     */
    public function orderCost()
    {
        if ($this->request->isAjax()) {
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            \think\Log::info('订单成本筛选参数: ' . json_encode($where));
            
            // 处理字段名冲突，明确指定表别名
            $query = Db::name('scanwork_order_cost')
                ->alias('c')
                ->join('scanwork_order o', 'c.order_id = o.id')
                ->join('scanwork_product p', 'c.product_id = p.id', 'LEFT');
            
            // 手动处理筛选条件以避免字段名冲突
            $filter = $this->request->param('filter', '');
            if ($filter) {
                $filterData = json_decode($filter, true);
                if (isset($filterData['order_no']) && $filterData['order_no']) {
                    $query->where('o.order_no', 'like', '%' . $filterData['order_no'] . '%');
                }
                if (isset($filterData['product_name']) && $filterData['product_name']) {
                    $query->where('p.name', 'like', '%' . $filterData['product_name'] . '%');
                }
                if (isset($filterData['order_status']) && $filterData['order_status']) {
                    $query->where('o.status', $filterData['order_status']);
                }
            }
            
            $list = $query->field('c.*, o.order_no, o.status as order_status, p.name as product_name')
                ->order($sort, $order)
                ->paginate($limit);
            
            // 获取汇总统计
            $summary = Db::name('scanwork_order_cost')
                ->field('
                    COUNT(*) as total_orders,
                    SUM(total_cost) as total_cost,
                    AVG(profit_rate) as avg_profit_rate,
                    SUM(profit) as total_profit
                ')
                ->find();
            
            return json([
                "total" => $list->total(), 
                "rows" => $list->items(),
                "summary" => $summary
            ]);
        }
        return $this->view->fetch();
    }

    /**
     * 计划成本
     */
    public function planCost()
    {
        if ($this->request->isAjax()) {
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            \think\Log::info('计划成本筛选参数: ' . json_encode($where));
            
            $list = Db::name('scanwork_plan_cost')
                ->alias('c')
                ->join('scanwork_production_plan p', 'c.plan_id = p.id', 'LEFT')
                ->where($where)
                ->field('c.*, p.plan_code, p.plan_name, c.status as plan_status')
                ->order($sort, $order)
                ->paginate($limit);
            
            // 获取汇总统计
            $summary = Db::name('scanwork_plan_cost')
                ->field('
                    COUNT(*) as total_plans,
                    SUM(total_cost) as total_cost,
                    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as executing_count,
                    SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) as completed_count
                ')
                ->find();
            
            return json([
                "total" => $list->total(), 
                "rows" => $list->items(),
                "summary" => $summary
            ]);
        }
        return $this->view->fetch();
    }

    /**
     * 成本差异分析
     */
    public function variance()
    {
        if ($this->request->isAjax()) {
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            
            $list = Db::name('scanwork_cost_variance')
                ->alias('v')
                ->join('scanwork_order o', 'v.order_id = o.id', 'LEFT')
                ->join('scanwork_production_plan p', 'v.plan_id = p.id', 'LEFT')
                ->where($where)
                ->field('v.*, o.order_no, p.plan_code')
                ->order($sort, $order)
                ->paginate($limit);
            
            // 获取汇总统计
            $summary = Db::name('scanwork_cost_variance')
                ->field('
                    SUM(CASE WHEN variance_amount < 0 THEN ABS(variance_amount) ELSE 0 END) as saving_amount,
                    SUM(CASE WHEN variance_amount > 0 THEN variance_amount ELSE 0 END) as overrun_amount,
                    SUM(variance_amount) as net_variance
                ')
                ->find();
            
            return json([
                "total" => $list->total(), 
                "rows" => $list->items(),
                "summary" => $summary
            ]);
        }
        return $this->view->fetch();
    }

    /**
     * 成本分析
     */
    public function analysis()
    {
        $startDate = $this->request->get('start_date', date('Y-m-01'));
        $endDate = $this->request->get('end_date', date('Y-m-d'));

        if ($this->request->isAjax()) {
            $data = [
                'summary' => Db::name('scanwork_order_cost')
                    ->where('cost_date', 'between', [strtotime($startDate), strtotime($endDate)])
                    ->field('
                        SUM(material_cost) as total_material,
                        SUM(labor_cost) as total_labor,
                        SUM(overhead_cost) as total_overhead,
                        SUM(total_cost) as total_cost,
                        SUM(profit) as total_profit,
                        AVG(profit_rate) as avg_profit_rate
                    ')
                    ->find(),
                'trend' => Db::name('scanwork_order_cost')
                    ->where('cost_date', 'between', [strtotime($startDate), strtotime($endDate)])
                    ->field('DATE(FROM_UNIXTIME(cost_date)) as date, SUM(total_cost) as cost, SUM(profit) as profit')
                    ->group('DATE(FROM_UNIXTIME(cost_date))')
                    ->select()
            ];
            
            return json(['code' => 1, 'data' => $data]);
        }

        $this->view->assign('startDate', $startDate);
        $this->view->assign('endDate', $endDate);
        return $this->view->fetch();
    }

    /**
     * 生成成本数据
     */
    public function generateData()
    {
        // 获取统计数据
        $orderCount = Db::name('scanwork_order')->count();
        $planCount = Db::name('scanwork_production_plan')->count();
        $costCount = Db::name('scanwork_order_cost')->count();
        $varianceCount = Db::name('scanwork_cost_variance')->count();
        
        $this->view->assign('orderCount', $orderCount);
        $this->view->assign('planCount', $planCount);
        $this->view->assign('costCount', $costCount);
        $this->view->assign('varianceCount', $varianceCount);
        
        if ($this->request->isPost()) {
            Db::startTrans();
            try {
                // 生成订单成本数据
                $orders = Db::name('scanwork_order')->select();
                foreach ($orders as $order) {
                    $existing = Db::name('scanwork_order_cost')->where('order_id', $order['id'])->find();
                    if (!$existing) {
                        // 基于实际业务数据计算成本
                        $materialCost = $this->calculateMaterialCost($order['id']);
                        $laborCost = $this->calculateLaborCost($order['id']);
                        $equipmentCost = $this->calculateEquipmentCost($order['id']);
                        $energyCost = $this->calculateEnergyCost($order['id']);
                        $qualityCost = $this->calculateQualityCost($order['id']);
                        $overheadCost = $equipmentCost + $energyCost + $qualityCost; // 制造费用 = 设备+能源+质量
                        $totalCost = $materialCost + $laborCost + $overheadCost; // 总成本 = 材料+人工+制造费用
                        $sellingPrice = $totalCost * (1 + rand(10, 30) / 100); // 10-30%利润
                        $profit = $sellingPrice - $totalCost;
                        $profitRate = $totalCost > 0 ? ($profit / $totalCost) * 100 : 0;

                        Db::name('scanwork_order_cost')->insert([
                            'order_id' => $order['id'],
                            'order_no' => $order['order_no'],
                            'product_id' => rand(1, 10),
                            'model_id' => rand(1, 5),
                            'quantity' => $order['total_quantity'],
                            'material_cost' => $materialCost,
                            'labor_cost' => $laborCost,
                            'equipment_cost' => $equipmentCost,
                            'energy_cost' => $energyCost,
                            'quality_cost' => $qualityCost,
                            'overhead_cost' => $overheadCost,
                            'total_cost' => $totalCost,
                            'unit_cost' => $order['total_quantity'] > 0 ? $totalCost / $order['total_quantity'] : 0,
                            'selling_price' => $sellingPrice,
                            'profit' => $profit,
                            'profit_rate' => $profitRate,
                            'cost_date' => strtotime(date('Y-m-d', strtotime('-' . rand(1, 30) . ' days'))),
                            'status' => 1,
                            'remark' => '系统生成数据',
                            'createtime' => time(),
                            'updatetime' => time()
                        ]);
                    }
                }

                // 生成计划成本数据
                $plans = Db::name('scanwork_production_plan')->select();
                foreach ($plans as $plan) {
                    $existing = Db::name('scanwork_plan_cost')->where('plan_id', $plan['id'])->find();
                    if (!$existing) {
                        $plannedCost = rand(2000, 8000);
                        $actualCost = $plannedCost * (1 + rand(-20, 20) / 100); // 实际成本在计划成本的80%-120%之间
                        $costVariance = $actualCost - $plannedCost;
                        $varianceRate = $plannedCost > 0 ? ($costVariance / $plannedCost) * 100 : 0;

                        Db::name('scanwork_plan_cost')->insert([
                            'plan_id' => $plan['id'],
                            'plan_no' => $plan['plan_code'],
                            'material_cost' => rand(1000, 3000),
                            'labor_cost' => rand(500, 1500),
                            'equipment_cost' => rand(200, 800),
                            'overhead_cost' => rand(300, 1000),
                            'total_cost' => $plannedCost,
                            'actual_cost' => $actualCost,
                            'cost_variance' => $costVariance,
                            'cost_date' => strtotime(date('Y-m-d', strtotime('-' . rand(1, 30) . ' days'))),
                            'status' => rand(0, 2), // 0:待执行, 1:执行中, 2:已完成
                            'createtime' => time(),
                            'updatetime' => time()
                        ]);
                    }
                }

                // 生成成本差异数据
                $orderCosts = Db::name('scanwork_order_cost')->select();
                foreach ($orderCosts as $cost) {
                    $existing = Db::name('scanwork_cost_variance')->where('order_id', $cost['order_id'])->find();
                    if (!$existing) {
                        $varianceTypes = ['material', 'labor', 'overhead'];
                        $varianceType = $varianceTypes[array_rand($varianceTypes)];
                        
                        $plannedAmount = $cost['total_cost'];
                        $actualAmount = $plannedAmount * (1 + rand(-15, 15) / 100);
                        $varianceAmount = $actualAmount - $plannedAmount;
                        $varianceRate = $plannedAmount > 0 ? ($varianceAmount / $plannedAmount) * 100 : 0;

                        Db::name('scanwork_cost_variance')->insert([
                            'order_id' => $cost['order_id'],
                            'plan_id' => rand(1, 20),
                            'variance_type' => $varianceType,
                            'planned_cost' => $plannedAmount,
                            'actual_cost' => $actualAmount,
                            'variance_amount' => $varianceAmount,
                            'variance_rate' => $varianceRate,
                            'variance_reason' => '材料价格波动',
                            'improvement_action' => '优化采购渠道',
                            'analyst_id' => 1,
                            'analysis_date' => strtotime(date('Y-m-d', strtotime('-' . rand(1, 30) . ' days'))),
                            'createtime' => time()
                        ]);
                    }
                }

                Db::commit();
                $this->success('成本数据生成成功');
            } catch (Exception $e) {
                Db::rollback();
                $this->error('生成失败：' . $e->getMessage());
            }
        }
        return $this->view->fetch();
    }

    /**
     * 计算材料成本
     */
    private function calculateMaterialCost($orderId)
    {
        // 基于订单物料清单计算材料成本
        $materialCost = Db::name('scanwork_order_material')
            ->alias('om')
            ->join('scanwork_material m', 'om.material_id = m.id')
            ->where('om.order_id', $orderId)
            ->sum('om.required_quantity * m.price');
        
        return $materialCost ?: rand(1000, 5000); // 如果没有数据，使用随机值
    }

    /**
     * 计算人工成本
     */
    private function calculateLaborCost($orderId)
    {
        // 基于报工记录计算人工成本
        $laborCost = Db::name('scanwork_report')
            ->alias('r')
            ->join('scanwork_allocation a', 'r.allocation_id = a.id')
            ->where('a.order_id', $orderId)
            ->sum('r.wage');
        
        return $laborCost ?: rand(500, 2000); // 如果没有数据，使用随机值
    }

    /**
     * 计算设备成本
     */
    private function calculateEquipmentCost($orderId)
    {
        // 基于设备使用时间和小时成本计算
        $equipmentCost = Db::name('scanwork_equipment_operation')
            ->alias('eo')
            ->join('scanwork_equipment e', 'eo.equipment_id = e.id')
            ->join('scanwork_allocation a', 'eo.allocation_id = a.id')
            ->where('a.order_id', $orderId)
            ->sum('eo.operation_hours * e.hourly_cost');
        
        return $equipmentCost ?: rand(100, 500); // 如果没有数据，使用随机值
    }

    /**
     * 计算能源成本
     */
    private function calculateEnergyCost($orderId)
    {
        // 基于能源消耗记录计算
        $energyCost = Db::name('scanwork_energy_consumption')
            ->alias('ec')
            ->join('scanwork_equipment_operation eo', 'ec.equipment_id = eo.equipment_id')
            ->join('scanwork_allocation a', 'eo.allocation_id = a.id')
            ->where('a.order_id', $orderId)
            ->where('DATE(ec.consumption_date)', 'DATE(FROM_UNIXTIME(eo.operation_time))')
            ->sum('ec.total_cost');
        
        return $energyCost ?: rand(50, 200); // 如果没有数据，使用随机值
    }

    /**
     * 计算质量成本
     */
    private function calculateQualityCost($orderId)
    {
        // 基于质量记录计算质量成本
        $qualityCost = Db::name('scanwork_quality_record')
            ->alias('qr')
            ->join('scanwork_report r', 'qr.report_id = r.id')
            ->join('scanwork_allocation a', 'r.allocation_id = a.id')
            ->where('a.order_id', $orderId)
            ->sum('qr.quality_cost');
        
        return $qualityCost ?: rand(50, 300); // 如果没有数据，使用随机值
    }
}

